import transaction

from hashlib import md5

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.orm import relationship, backref
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from sqlalchemy import MetaData
import sqlalchemy.orm.exc

from zope.sqlalchemy import ZopeTransactionExtension


#TODO figure out where this is supposed to come from
#from cpswarehouse.model import meta

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
meta = MetaData()


#Dimension tables:
year_dimension = sa.Table("year_dimension", meta,
    sa.Column("year", sa.types.Integer, primary_key=True), 
    )

grade_dimension = sa.Table("grade_dimension", meta,
                           #TODO figure out what things like "80" and "9x" are...then coerce into int?
                           sa.Column("grade", sa.types.String(255), primary_key=True),
                           sa.UniqueConstraint("grade"),
                           )

school_dimension = sa.Table("school_dimension", meta,
                            sa.Column("id", sa.types.String(36), primary_key=True),
                            sa.Column("name", sa.types.String(255), nullable=False),
                            sa.Column("unit", sa.types.Integer, nullable=False),
                            sa.Column("address", sa.types.String(255)), 
                            sa.Column("latitude", sa.types.Float),
                            sa.Column("longitude", sa.types.Float),
                            sa.UniqueConstraint("unit"),
                            )
#Fact tables:
racial_fact = sa.Table("racial_fact", meta,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("year_id", sa.ForeignKey("year_dimension.year")),
    sa.Column("grade_id", sa.ForeignKey("grade_dimension.grade")),
    sa.Column("school_id", sa.ForeignKey("school_dimension.id")),
    sa.Column("nativeamerican", sa.types.Integer, default=0), 
    sa.Column("asian", sa.types.Integer, default=0), 
    sa.Column("black", sa.types.Integer, default=0), 
    sa.Column("hispanic", sa.types.Integer, default=0), 
    sa.Column("multiracial", sa.types.Integer, default=0), 
    sa.Column("white", sa.types.Integer, default=0), 
    sa.Column("total", sa.types.Integer, default=0),                       
    )


class UniqueOnly(object):
    """
    Superclass for dimensions so that they can be looked up (using the
    key that subclasses are required to implement) at load time to
    avoid duplicates and have everything be inserted at once.

    This is suitable for a one time load _only_.
    
    TODO this could probably be factored into something like the
    decorator pattern or just a simple lookup function.  It doesn't
    really need to be in a super-class since the logic is only used at
    load time.
    """
    
    @classmethod
    def get_instance(cls, *args, **kwargs):
        """Call this method to instantiate or load the dimension from
        the cache. All args and kwargs are passed on to the class'
        __init__."""
        instance = cls(*args, **kwargs)

        session = DBSession()
        
        if cls.items.has_key(instance.get_primary_key()):
            return cls.items[instance.get_primary_key()]
        else:
            #check to see if it was already defined in the db and replace our current instance with that one if it was.
            looked_up = instance.db_lookup(session) 
            if looked_up is None:
                session.add(instance)
            else:
                instance = looked_up
                
            cls.items[instance.get_primary_key()] = instance
            return instance


    def db_lookup(self, session):
        try:
            args = {self.get_primary_key_name(): self.get_primary_key()}
            print args
            instance = session.query(self.__class__).filter_by(**args).one()
            print "found"
            return instance
        except sqlalchemy.orm.exc.NoResultFound:
            return None


    def get_primary_key_name(self):
        """Subclasses must implement this method which the
        get_instance class method uses to determine uniqueness.  """
        raise Exception("Not Implemented")        

    
    def get_primary_key(self):
        """Subclasses must implement this method which the
        get_instance class method uses to determine uniqueness.  """
        return getattr(self, self.get_primary_key_name())


class YearDimension(UniqueOnly):
    items = dict()

    def __init__(self, year):
        self.year = year

    def get_primary_key_name(self):
        return "year"

    def __unicode__(self):
        return u"%s" % self.year


class GradeDimension(UniqueOnly):
    items = dict()

    def __init__(self, grade):
        self.grade = grade

    def get_primary_key_name(self):
        return "grade"

    def __unicode__(self):
        return u"%s" % self.grade


class SchoolDimension(UniqueOnly):
    items = dict()

    def __init__(self, name, unit):
        self.name = name
        self.unit = unit

        key_hash = md5()
        key_hash.update(name)
        self.id = key_hash.hexdigest()
        

    def get_primary_key_name(self):
        return "unit"


    def __unicode__(self):
        return u"%s" % self.name


class RacialFact(object):
    def __init__(self,
                 year,
                 grade,
                 school,
                 nativeamerican=None,
                 asian=None,
                 black=None,
                 hispanic=None,
                 multiracial=None,
                 white=None):
        self.year_id = year.year
        self.grade_id = grade.grade
        self.school_id = school.id
        self.nativeamerican = nativeamerican
        self.asian = asian
        self.black = black
        self.hispanic = hispanic
        self.multiracial = multiracial
        self.white = white


    def __unicode__(self):
        return u"%s: %s" % (self.year_id, self.grade_id)


orm.mapper(YearDimension, year_dimension)
orm.mapper(GradeDimension, grade_dimension)
orm.mapper(SchoolDimension, school_dimension, )
orm.mapper(RacialFact, racial_fact, 
           properties={'school': relationship(SchoolDimension, backref='racial_facts'),
                       'year': relationship(YearDimension, backref='racial_facts'),
                       'grade': relationship(GradeDimension, backref='racial_facts'),
                       })

    
    
def initialize_sql(engine):

    DBSession.configure(bind=engine)
    #Base.metadata.bind = engine
    #Base.metadata.create_all(engine)

    meta.bind = engine

    #filename = os.path.split(conf.filename)[-1]
    #if filename == 'test.ini':
    #    # Permanently drop any existing tables
    #    log.info("Dropping existing tables...")
    #    meta.drop_all(checkfirst=True)
        
        
    # Create the tables if they don't already exist
    #
    
    try:
        session = DBSession()
        meta.create_all(bind=engine)
        #model = MyModel(name=u'root',value=55)
        #session.add(model)
        #session.flush()
        transaction.commit()
    except IntegrityError:
        DBSession.rollback()
